﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using Pub;

namespace JT_DBMgr
{
    public class CDBTree_Access : IDBTree
    {
        public string S_Con;

        public string access_con;
        public OleDbCommand cm;
        public OleDbConnection cn;

        public OleDbDataAdapter da;
        public int ds_count = 0;
        public OleDbParameter[] pars;
        public List<OleDbParameter> pars2;
        public string sqlServer_con;
        public string s_sql = "";

      
        public List<DBTreeInfo> lstDBInf = new List<DBTreeInfo>();

        public CDBTree_Access()
        {
            db_init();
        }

        public void db_init()
        {
            string sDBCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Application.StartupPath + "\\DBCon.mdb";
            cn = new OleDbConnection(sDBCon);
            lstDBInf.Clear();

            DataSet ds1 = sel1("SELECT * FROM T_DBTree ORDER BY [Last] DESC");
            if (!CDB.dsIsNull(ds1))
            {
                for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
                {
                    DataRow dr1 = ds1.Tables[0].Rows[i];
                    DBTreeInfo dbInf_Tem    = new DBTreeInfo();
                    dbInf_Tem.sID           = dr1["ID"].ToString();
                    dbInf_Tem.sServ         = dr1["Server"].ToString();
                    dbInf_Tem.sD_B_         = dr1["DataBase"].ToString();
                    dbInf_Tem.sUser         = dr1["User"].ToString();
                    dbInf_Tem.sPass         = dr1["Pass"].ToString();

                    dbInf_Tem.sRootID       = dr1["RootID"].ToString();
                    dbInf_Tem.sTabName      = dr1["sTabName"].ToString();
                    dbInf_Tem.sPreCol       = dr1["sPreCol"].ToString();
                    dbInf_Tem.sIDCol        = dr1["sIDCol"].ToString();
                    dbInf_Tem.sName         = dr1["sName"].ToString();
                    dbInf_Tem.sOrderID      = dr1["sOrderID"].ToString();
                    dbInf_Tem.sTagID        = dr1["sTagID"].ToString();
                    dbInf_Tem.sTabDataNameS = dr1["TabDataName"].ToString();
                    dbInf_Tem.sTabDataIDS   = dr1["TabDataID"].ToString();
                    dbInf_Tem.sCaption      = dr1["Caption"].ToString();

                    lstDBInf.Add(dbInf_Tem);
                }
            }
        }

        public DataSet sel1(string sql)
        {
            try
            {
                this.s_sql = sql;
                cn.Open();
                da = new OleDbDataAdapter(sql, cn);
                DataSet ds = new DataSet();

                da.Fill(ds);
                OleDbCommandBuilder sqlCb = new OleDbCommandBuilder(da);

                cn.Close();

                //da.FillSchema(ds, SchemaType.Mapped);
                try
                {
                    if (ds == null || ds.Tables[0].Rows.Count == 0)
                    {
                        ds_count = 0;
                    }

                    ds_count = ds.Tables[0].Rows.Count;
                }
                catch (System.Exception ex1)
                {
                    ds_count = 0;
                    CDebug.Send("返回结果个数出错", ex1);
                }

                return ds;

            }
            catch (Exception ex)
            {
                if (cn.State != System.Data.ConnectionState.Closed) cn.Close();

                CDebug.Send(sql + "; " + ex.ToString());
                CDebug.Send("sql语句错误", ex);
                ds_count = 0;
                return null;
            }
        }

        public int cmd1(string sql)
        {
            cn.Open();
            cm = new OleDbCommand();
            cm.Connection = cn;

            cm = new OleDbCommand(sql, cn);

            int i = 0;
            try
            {
                i = cm.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
            }

            cn.Close();
            return i;

        }

        public List<DBTreeInfo> getDBInfo(string sServ)
        {
            List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
            for (int i = 0; i < lstDBInf.Count; i++)
            {
                if (lstDBInf[i].sServ.ToUpper() == sServ.ToUpper())
                {
                    dbInf.Add(lstDBInf[i]);
                }
            }

            return dbInf;
        }

        private List<DBTreeInfo> getDBInfo(string sServ, string sD_B_)
        {
            List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
            for (int i = 0; i < lstDBInf.Count; i++)
            {
                if (lstDBInf[i].sServ.ToUpper() == sServ.ToUpper() && lstDBInf[i].sD_B_.ToUpper() == sD_B_.ToUpper())
                {
                    dbInf.Add(lstDBInf[i]);
                }
            }

            return dbInf;
        }

        private List<DBTreeInfo> getDBInfo(string sServ, string sD_B_, string sTabl)
        {
            List<DBTreeInfo> lstInfo_Temp = new List<DBTreeInfo>();
            for (int i = 0; i < lstDBInf.Count; i++)
            {
                if (lstDBInf[i].sServ.ToUpper() == sServ.ToUpper() && lstDBInf[i].sD_B_.ToUpper() == sD_B_.ToUpper()
                    && lstDBInf[i].sTabName.ToUpper() == sTabl.ToUpper())
                {
                    DBTreeInfo dbInf = lstDBInf[i];
                    lstInfo_Temp.Add(dbInf);

                }
            }

            return lstInfo_Temp;
        }


        public DBTreeInfo getDBInfo(string sServ, string sD_B_, string sTabl, string sCapt)
        {
            DBTreeInfo dbInf = new DBTreeInfo();
            for (int i = 0; i < lstDBInf.Count; i++)
            {
                if (lstDBInf[i].sServ.ToUpper() == sServ.ToUpper() && lstDBInf[i].sD_B_.ToUpper() == sD_B_.ToUpper()
                    && lstDBInf[i].sTabName.ToUpper() == sTabl.ToUpper() && lstDBInf[i].sCaption.ToUpper() == sCapt.ToUpper())
                {
                    dbInf = lstDBInf[i];
                    break;
                }
            }

            return dbInf;
        }

        public string getDBCon(DBTreeInfo dbInf)
        {
            return "Data Source=" + dbInf.sServ + ";Initial Catalog=" + dbInf.sD_B_.Split(';')[0]
                + ";Persist Security Info=True;User ID=" + dbInf.sUser + ";Password=" + dbInf.sPass;
        }

        public string getDBCon(DBTreeInfo dbInf, string sD_B_)
        {
            return "Data Source=" + dbInf.sServ + ";Initial Catalog=" + sD_B_
                + ";Persist Security Info=True;User ID=" + dbInf.sUser + ";Password=" + dbInf.sPass;
        }

        public string getDBCon(string sServ, string sD_B_)
        {
            DBTreeInfo dbInf = new DBTreeInfo();
            dbInf = getDBInfo(sServ)[0];
            return "Data Source=" + dbInf.sServ + ";Initial Catalog=" + sD_B_
                + ";Persist Security Info=True;User ID=" + dbInf.sUser + ";Password=" + dbInf.sPass;
        }

        /// <summary>
        /// 返回数据库名称列表
        /// </summary>
        /// <param name="sServ"></param>
        /// <returns></returns>
        public List<string> getDBNam(string sServ)
        {
            List<string> lstDBNam = new List<string>();
            List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
            dbInf = getDBInfo(sServ);

            for (int i = 0; i < dbInf.Count; i++)
            {
                Boolean isDBNam = false;
                for (int j = 0; j < lstDBNam.Count; j++)
                {
                    if (lstDBNam[j].ToUpper() == dbInf[i].sD_B_.ToUpper())
                    {
                        isDBNam = true;
                        break;
                    }
                }

                if (isDBNam == false)
                {
                    string sDBNam = dbInf[i].sD_B_;
                    lstDBNam.Add(sDBNam);
                }
            }

            return lstDBNam;
        }

        /// <summary>
        /// 返回表名称列表
        /// </summary>
        /// <param name="sServ"></param>
        /// <param name="sD_B_"></param>
        /// <returns></returns>
        public List<string> getTabNam(string sServ, string sD_B_)
        {
            List<string> lstTabNam = new List<string>();
            List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
            dbInf = getDBInfo(sServ, sD_B_);

            for (int i = 0; i < dbInf.Count; i++)
            {
                Boolean isTabl = false;
                for (int j = 0; j < lstTabNam.Count; j++)
                {
                    if (lstTabNam[j].ToUpper() == dbInf[i].sTabName.ToUpper())
                    {
                        isTabl = true;
                        break;
                    }
                }
                if (isTabl == false)
                {
                    string sDBNam = dbInf[i].sTabName;
                    lstTabNam.Add(sDBNam);
                }
            }

            return lstTabNam;
        }

        public List<string> getCaption(string sServ, string sD_B_, string sTabl)
        {
            List<string> lstCaption = new List<string>();
            List<DBTreeInfo> dbInf = new List<DBTreeInfo>();
            dbInf = getDBInfo(sServ, sD_B_, sTabl);

            for (int i = 0; i < dbInf.Count; i++)
            {
                string sDBNam = dbInf[i].sCaption;
                lstCaption.Add(sDBNam);
            }

            return lstCaption;
        }

    }
}
